package org.tea.inventory.db.util.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class ExcelReader {
    /**
     * @描述：根据文件名读取excel文件
     */
    public List<List<String>> read(String filePath) {
        List<List<String>> dataLst = new ArrayList<List<String>>();
        InputStream is = null;
        try {

            /** 调用本类提供的根据流读取的方法 */
            Workbook wb = WorkbookFactory.create(filePath);
            dataLst = readexcel(wb);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return dataLst;
    }

    public static Workbook getWorkbook(String filePath) throws Exception{

        Workbook wb = WorkbookFactory.create(filePath);

        return wb;
    }

    public List<List<String>> readExcel(String filePath) throws Exception{
        List<List<String>> dataLst = new ArrayList<List<String>>();
        InputStream is = null;
        try {

            Workbook wb = WorkbookFactory.create(filePath);
            dataLst = readexcel(wb);
            is.close();
        } catch (IOException e) {
            throw new IOException(e);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    // e.printStackTrace();
                    throw new IOException(e);
                }
            }
        }
        return dataLst;
    }

    public List<List<String>> readExcel2(String filePath) throws Exception{
        List<List<String>> dataLst = new ArrayList<List<String>>();
        InputStream is = null;
        try {

            Workbook wb = WorkbookFactory.create(filePath);
            dataLst = readexcel2(wb);
            is.close();
        } catch (IOException e) {
            throw new IOException(e);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    // e.printStackTrace();
                    throw new IOException(e);
                }
            }
        }
        return dataLst;
    }

    /**
     * @throws IOException
     * @描述：读取数据
     */
    private List<List<String>> readexcel(Workbook wb) throws IOException {

        /** 得到总的shell */
        //int sheetAccount = wb.getNumberOfSheets();
        /** 得到第一个shell */
        Sheet sheet = wb.getSheetAt(0);
        /** 得到Excel的行数 */
        List<List<String>> dataLst = readexcelSheet(sheet);

        wb.close();
        return dataLst;
    }


    private List<List<String>> readexcel2(Workbook wb) throws IOException {

        /** 得到总的shell */
        int sheetAccount = wb.getNumberOfSheets();
        if(sheetAccount>=1){
            /** 得到第一个shell */
            Sheet sheet = wb.getSheetAt(1);
            /** 得到Excel的行数 */
            List<List<String>> dataLst = readexcelSheet(sheet);

            wb.close();
            return dataLst;
        }
        return null;
    }


    private List<List<String>> readexcelSheet(Sheet sheet)throws IOException{
        List<List<String>> dataLst = new ArrayList<List<String>>();

        int rowCount = sheet.getPhysicalNumberOfRows();
        /** 也可以通过得到最后一行数 */
        //int lastRowNum = sheet.getLastRowNum();
        /** 循环Excel的行 */
        for (int r = 0; r < rowCount; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                continue;
            }
            List<String> rowLst = new ArrayList<String>();
            /** 循环Excel的列 */
            for (int c = 0; c < row.getLastCellNum(); c++) {
                Cell cell = row.getCell(c);
                String cellValue = "";
                if (null != cell) {
                    // 以下是判断数据的类型
                    switch (cell.getCellType()) {
                        // XSSFCell可以达到相同的效果
                        case NUMERIC: // 数字
                            double d = cell.getNumericCellValue();
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
                                // Date date = cell.getDateCellValue();
                                Date date = HSSFDateUtil.getJavaDate(d);
                                cellValue = new SimpleDateFormat(
                                        "yyyy-MM-dd HH:mm:ss").format(date);
                                //cellValue = cell.getDateCellValue() + "";
                            } else {// 数值类型

                                // 是否为数值型
                                DecimalFormat df = new DecimalFormat("#");
                                //System.out.println("type666=="+df.format(cell.getNumericCellValue()));
                                cellValue = df.format(cell.getNumericCellValue());

                                //cellValue = String.valueOf((int)cell.getNumericCellValue());
                            }

                            break;
                        case STRING: // 字符串
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN: // Boolean
                            cellValue = cell.getBooleanCellValue() + "";
                            break;
                        case FORMULA: // 公式
                            cellValue = cell.getCellFormula() + "";
                            break;
                        case BLANK: // 空值
                            cellValue = "";
                            break;
                        case ERROR: // 故障
                            cellValue = "非法字符";
                            break;
                        default:
                            cellValue = "未知类型";
                            break;
                    }
                }
                //System.out.print(cellValue + "\t");
                rowLst.add(cellValue);
            }
            //System.out.println();
            dataLst.add(rowLst);
        }
        return dataLst;
    }

    public List<List<String>> read(InputStream inputStream) {
        List<List<String>> dataLst = new ArrayList<List<String>>();
        InputStream is = null;
        try {

            Workbook wb = WorkbookFactory.create(inputStream);
            dataLst = readexcel(wb);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (is != null) {
                try {
                    is.close();
                } catch (IOException e) {
                    is = null;
                    e.printStackTrace();
                }
            }
        }
        return dataLst;
    }

    public static Object getValue(Cell cell){
        Object obj = null;

        if(cell != null){
            switch(cell.getCellType()){
                case NUMERIC:
                    double d = cell.getNumericCellValue();
                    if(HSSFDateUtil.isCellDateFormatted(cell)){
                        Date date = HSSFDateUtil.getJavaDate(d);
                        obj = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
                    } else {
                        obj = NumberToTextConverter.toText(d);
                    }
                    break;
                case STRING:
                    obj = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    obj = cell.getCellFormula();
                    break;
                case BLANK:
                    obj = "";
                    break;
                case ERROR:
                    break;
                default :
                    break;
            }
        }

        return obj;
    }
}
